#!/usr/bin/env python
# -*- coding:utf8 -*-

"""
@version: 
@author: lh
@license: Apache Licence 
@contact: liuhuan0672@gmail.com
@site: 
@software: PyCharm
@file: 0017.py
@time: 2016/1/21 15:56

第 0017 题： 将 第 0014 题中的 student.xls 文件中的内容写到 student.xml 文件中，如
下所示：
<?xml version="1.0" encoding="UTF-8"?>
<root>
<students>
<!--
    学生信息表
    "id" : [名字, 数学, 语文, 英文]
-->
{
    "1" : ["张三", 150, 120, 100],
    "2" : ["李四", 90, 99, 95],
    "3" : ["王五", 60, 66, 68]
}
</students>
</root>
"""

# python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库
from xml.dom import minidom

import xlrd as xlrd


def open_xls():
    excel = xlrd.open_workbook('./result/student.xls')
    student_sheet = excel.sheet_by_name('student')
    sheet_content = {}
    for row in range(student_sheet.nrows):
        row_value = student_sheet.row_values(row)
        for i in range(len(row_value)):
            if type(row_value[i]) == float:
                row_value[i] = int(row_value[i])
        sheet_content[str(row + 1)] = row_value[1:]
    return sheet_content


def build_xml(content):
    # Create Dom object
    doc = minidom.Document()
    # create root tag
    root = doc.createElement('root')
    doc.appendChild(root)
    # create 'students' tag
    students = doc.createElement('students')
    root.appendChild(students)
    # create comment element
    comment = doc.createComment("学生信息表\"id\":[名字，数学，语文，英文]")
    students.appendChild(comment)
    # create txt element
    students.appendChild(doc.createTextNode(str(content)))

    # save xml file
    student_xml = open('./result/student.xml', 'w', encoding="utf-8")
    student_xml.write(doc.toprettyxml())
    student_xml.close()


if __name__ == '__main__':
    build_xml(open_xls())
